﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ProdeManagmente.Libs;
using System.Data;
using ProdeManagmente.Model;

namespace ProdeManagmente.DAO
{
    public class MatchService
    {
        private static MatchService instance;
        private static String TBMatch = "Match";
        //private static String TBTeamTournament = "TeamTournament";

        private MatchService()
        { }

        public static MatchService getMatchServiceInstance()
        {
            if (instance == null)
            {
                instance = new MatchService();
            }
            return instance;
        }

        public Match getMatchById(long id)
        {
            Match match = null;

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBMatch + " " +
                            "WHERE ID = " + id;

            DataTable matchTable = conn.executeQuery(SQL);


            foreach (DataRow matchRow in matchTable.Rows)
            {
                match = getMatch(matchRow, new List<Team>(), new List<Tournament>(), new List<Result>());
            }
            if (match == null)
            {
                throw new BusinessException("301", "No se encontro el elemento solicidado");
            }
            return match;
        }

        /**
         * Obtiene la lista de equipos,  filtro SIN la palabra clave WHERE
         */
        public List<Match> getMatchListFilter(String filter)
        {
            TeamService teamService = TeamService.getTeamServiceInstance();
            TournamenteService tournamentService = TournamenteService.getTournamenteServiceInstance();
            ResultService resultService = ResultService.getResultServiceInstance();

            List<Match> matchList = new List<Match>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT * FROM " + TBMatch + " ";

            if (!filter.Trim().Equals(""))
            {
                SQL = SQL + "WHERE " + filter;
            }

            DataTable matchTable = conn.executeQuery(SQL);
            List<Team> teams = new List<Team>();
            teams.AddRange(teamService.getTeamListFilter(""));

            List<Tournament> tournaments = new List<Tournament>();
            tournaments.AddRange(tournamentService.getTournamentListFilter(""));

            List<Result> results = new List<Result>();
            results.AddRange(resultService.getResultListFilter(""));

            foreach (DataRow matchRow in matchTable.Rows)
            {
                Match match = getMatch(matchRow, teams, tournaments, results);
                matchList.Add(match);
            }

            return matchList;
        }

        public bool tournamentHasMatch(long idTournament)
        {
            List<Match> matchList = new List<Match>();

            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT Top(1) * FROM " + TBMatch + " " +
                         "WHERE idTournament = " + idTournament;

            DataTable matchTable = conn.executeQuery(SQL);

            foreach (DataRow matchRow in matchTable.Rows)
            {
                return true;
            }

            return false;
        }

        public Match save(Match match)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";
            if (match.getId() != 0L) //Si es diferente de null es por que ya existe entonces tiene que actualizarlo
            {
                SQL = "UPDATE  " + TBMatch + " SET " +
                      "idTournament = " + match.getTournament().getId() + ", " +
                      "idTeam1 = " + match.getTeam1().getId() + ", " +
                      "idTeam2 = " + match.getTeam2().getId() + ", " +
                      "Date = '" + match.getDate().ToString("o") + "', " +
                      "Time = '" + match.getTime().ToString("HH:mm:ss.ffffzzz") + "' " +
                      "WHERE id = " + match.getId() + " ";
            }
            else //Si es null es por que es un objeto nuevo
            {
                match.setId(getNewId());
                SQL = "INSERT INTO " + TBMatch + " " +
                       "(id, idTournament, idTeam1, idTeam2, Date, Time) VALUES (" +
                       "" + match.getId() + ", " +
                       "" + match.getTournament().getId() + ", " +
                       "" + match.getTeam1().getId() + ", " +
                       "" + match.getTeam2().getId() + ", " +
                       "'" + match.getDate().ToString("o") + "', " +
                       "'" + match.getTime().ToString("HH:mm:ss.ffffzzz") + "')";
            }

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("302", "No se pudo grabar el elemento");
            }

            return match;
        }

        public void delete(long id)
        {
            Persistencia conn = Persistencia.getInstance();
            String SQL = "";

            SQL = "DELETE  " + TBMatch + "  " +
                    "WHERE id = " + id + " ";

            int result = Convert.ToInt32(conn.ExecuteNonQuery(SQL));
            if (result <= 0)
            {
                throw new BusinessException("303", "No se pudo borrar el elemento");
            }
        }

        private Match getMatch(DataRow matchRow, List<Team> teams, List<Tournament> tournaments, List<Result> results)
        {
            Match match = null;
            TournamenteService tournamenteService = TournamenteService.getTournamenteServiceInstance();
            TeamService teamService = TeamService.getTeamServiceInstance();

            

            long idMatch = Convert.ToInt64(matchRow["id"].ToString());
            long idTournament = Convert.ToInt64(matchRow["idTournament"].ToString());
            long idTeam1 = Convert.ToInt64(matchRow["idTeam1"].ToString());
            long idTeam2 = Convert.ToInt64(matchRow["idTeam2"].ToString());

            Team team1 = null;
            Team team2 = null;
            Tournament tournament = null;
            bool hasResult = false;

            team1 = teams.Find(x => x.getId() == idTeam1);
            team2 = teams.Find(x => x.getId() == idTeam2);
            tournament = tournaments.Find(x => x.getId() == idTournament);
            hasResult = results.Find(x => x.getMatch().getId() == idMatch) != null;

            if (team1 == null)
            {
                team1 = teamService.getTeamById(idTeam1);                
            }

            if (team2 == null)
            {
                team2 = teamService.getTeamById(idTeam2);
            }

            if (tournament == null)
            {
                tournament = tournamenteService.getTournamentById(idTournament);
            }           

            match = new Match(idMatch,
                                tournament,
                                team1,
                                team2,
                                Convert.ToDateTime(matchRow["Date"].ToString()),
                                Convert.ToDateTime(matchRow["Time"].ToString()));

            match.hasResult = hasResult;

            return match;
        }

        private long getNewId()
        {
            long id = 0L;
            String strId;
            Persistencia conn = Persistencia.getInstance();
            String SQL = "SELECT MAX(id) FROM " + TBMatch + " ";

            DataTable matchTable = conn.executeQuery(SQL);

            foreach (DataRow matchRow in matchTable.Rows)
            {
                strId = matchRow[0].ToString();
                if (strId.Equals(""))
                {
                    strId = "0";
                }
                id = Convert.ToInt64(strId) + 1;
            }
            return id;
        }
    }
}
